package cc.iotkit.screen.service.impl;

import cc.iotkit.common.satoken.utils.LoginHelper;
import cc.iotkit.common.utils.StringUtils;
import cc.iotkit.screen.service.BizReportService;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;
import org.springframework.web.context.request.RequestContextHolder;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author chenx
 * @date 2024年07月22日
 */
@Service
@Slf4j
public class BizReportServiceImpl implements BizReportService {

	@PersistenceContext
	private EntityManager entityManager;

	/**
	 * 判断检索日期类型
	 * 1 日 %Y-%m-%d
	 * 2 月 %Y-%m
	 * 3 年 %Y
	 * @param queryType
	 * @return
	 */
	public String anaQueryType(Integer queryType){
		String res = "";
		if(queryType != null){
			switch(queryType){
				case 1:
					res = "%Y-%m-%d" ;
					break;
				case 2:
					res = "%Y-%m" ;
					break;
				case 3:
					res = "%Y" ;
					break;
				default:
					res = "";
					break;
			}
		}
		return res;
	}

	/**
	 * 格式化日期
	 * @param queryType
	 * @return
	 */
	public String dateFormatePattern(Integer queryType){
		String res = "";
		if(queryType != null){
			switch(queryType){
				case 1:
					res = "yyyy-MM-dd" ;
					break;
				case 2:
					res = "yyyy-MM" ;
					break;
				case 3:
					res = "yyyy" ;
					break;
				default:
					res = "";
					break;
			}
		}
		return res;
	}

	/**
	 * 获取当前登录用户的租户ID
	 * @return
	 */
	public Long getTenantId(){
		//超级管理员或系统进程调用
		if(LoginHelper.isSuperAdmin() || RequestContextHolder.getRequestAttributes()==null)
			return null;
		else {
			return Long.parseLong(LoginHelper.getTenantId());
		}
	}

	/**
	 * 围栏外告警
	 * @return
	 */
	@Override
	public List<Map<String, Object>> alertrecord() {
		Long tenantId = getTenantId();
		String sql= "";
		if(tenantId!=null){
			sql = "SELECT\n" +
					" count( 0 ) AS alertNum \n" +
					"FROM\n" +
					" alert_record";
		}else{
			sql = "SELECT\n" +
					" count( 0 ) AS alertNum \n" +
					"FROM\n" +
					" alert_record\n" +
					"WHERE tenant_id = "+tenantId;
		}

		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 设备状态展示
	 * @return
	 */
	@Override
	public List<Map<String, Object>> deviceStatus() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"SELECT\n" +
							" state AS deviceStatus,\n" +
							" count( state ) AS deviceNum \n" +
							"FROM\n" +
							" device_info \n" +
							"GROUP BY state \n " +
							"UNION\n" +
							"SELECT\n" +
							" 'failure' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" biz_workorder \n" +
							"WHERE\n" +
							" type = 3 \n " +
							"UNION\n" +
							"SELECT\n" +
							" 'total' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" device_info";
		}else{
			sql=
					"SELECT\n" +
							" state AS deviceStatus,\n" +
							" count( state ) AS deviceNum \n" +
							"FROM\n" +
							" device_info where tenant_id = "+tenantId+"\n" +
							"GROUP BY state \n " +
							"UNION\n" +
							"SELECT\n" +
							" 'failure' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" biz_workorder\n" +
							"WHERE\n" +
							" type = 3 and tenant_id = "+tenantId+"\n " +
							"UNION\n" +
							"SELECT\n" +
							" 'total' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" device_info where tenant_id = "+tenantId+"\n";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 区域设备汇总(省级)
	 * @return
	 */
	@Override
	public List<Map<String, Object>> areaDevice() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"SELECT \n" +
							" CONCAT (ba.id,'-',ba.area_name) as companyInfo,\n" +
							" count(di.id) as deviceNum\n" +
							"FROM device_info di \n" +
							"LEFT JOIN bas_area ba ON di.province_id = ba.id \n" +
							"WHERE di.province_id is not null AND ba.parent_id = 0\n" +
							"GROUP BY ba.id";
		}else{
			sql=
					"SELECT \n" +
							" CONCAT (ba.id,'-',ba.area_name) as companyInfo,\n" +
							" count(di.id) as deviceNum\n" +
							"FROM device_info di \n" +
							"LEFT JOIN bas_area ba ON di.province_id = ba.id \n" +
							"WHERE di.province_id is not null AND ba.parent_id = 0 AND di.tenant_id = "+tenantId+"\n" +
							"GROUP BY ba.id";
		}

		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	public String getSqlStr(Long id, Long tenantId){
		String sql = "";
		if(id != null){
			if(id == 36L || id == 37L || id == 108L || id == 269L ){
				if(tenantId!=null){
					sql =
							"SELECT\n" +
									"\tba.area_name AS companyName,\n" +
									"\tcount( di.id ) AS deviceNum \n" +
									"FROM\n" +
									"\tdevice_info di\n" +
									"\tLEFT JOIN bas_area ba ON di.county_id = ba.id \n" +
									"WHERE\n" +
									"\tdi.province_id IS NOT NULL \n" +
									"\tAND di.tenant_id ="+tenantId+"\n" +
									"\tAND ba.parent_id = "+id+"\n" +
									"GROUP BY ba.id";
				}else{
					sql = "SELECT\n" +
							"\tba.area_name AS companyName,\n" +
							"\tcount( di.id ) AS deviceNum \n" +
							"FROM\n" +
							"\tdevice_info di\n" +
							"\tLEFT JOIN bas_area ba ON di.county_id = ba.id \n" +
							"WHERE\n" +
							"\tdi.province_id IS NOT NULL \n" +
							"\tAND ba.parent_id = "+id+"\n" +
							"GROUP BY ba.id";
				}
			}else{
				if(tenantId!=null){
					sql =
							"SELECT\n" +
									"\tba.area_name AS companyName,\n" +
									"\tcount( di.id ) AS deviceNum \n" +
									"FROM\n" +
									"\tdevice_info di\n" +
									"\tLEFT JOIN bas_area ba ON di.city_id = ba.id \n" +
									"WHERE\n" +
									"\tdi.province_id IS NOT NULL \n" +
									"\tAND di.tenant_id ="+tenantId+"\n" +
									"\tAND ba.parent_id = "+id+"\n" +
									"GROUP BY ba.id";
				}else{
					sql=
							"SELECT\n" +
									"\tba.area_name AS companyName,\n" +
									"\tcount( di.id ) AS deviceNum \n" +
									"FROM\n" +
									"\tdevice_info di\n" +
									"\tLEFT JOIN bas_area ba ON di.city_id = ba.id \n" +
									"WHERE\n" +
									"\tdi.province_id IS NOT NULL \n" +
									"\tAND ba.parent_id = "+id+"\n" +
									"GROUP BY ba.id";
				}
			}
		}
		return sql;
	}
	/**
	 * 区域设备汇总(市级)
	 * @param id 省级ID
	 * @return
	 */
	@Override
	public List<Map<String, Object>> areaCityDevice(Long id) {
		Long tenantId = getTenantId();
		String sql = "";
		// 处理直辖市
		if(id != null){
			if(tenantId==null){
				if(id == 1L){
					// 北京市
					id = 36L;
					sql = getSqlStr(id, tenantId);
				}else if(id == 2L){
					// 天津市
					id = 37L;
					sql = getSqlStr(id, tenantId);
				}else if(id == 9L){
					// 上海市
					id = 108L;
					sql = getSqlStr(id, tenantId);
				}else if(id == 22L){
					// 重庆市
					id = 269L;
					sql = getSqlStr(id, tenantId);
				}else{
					sql = getSqlStr(id, tenantId);
				}
			}else{
				if(id == 1L){
					// 北京市
					id = 36L;
					sql = getSqlStr(id, tenantId);
				}else if(id == 2L){
					// 天津市
					id = 37L;
					sql = getSqlStr(id, tenantId);
				}else if(id == 9L){
					// 上海市
					id = 108L;
					sql = getSqlStr(id, tenantId);
				}else if(id == 22L){
					// 重庆市
					id = 269L;
					sql = getSqlStr(id, tenantId);
				}else{
					sql = getSqlStr(id, tenantId);
				}
			}
		}

		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * （设备）数量排名（省份）
	 * @return
	 */
	@Override
	public List<Map<String, Object>> sfnumRanking() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"select \n" +
							" ba.area_name as companyName,\n" +
							" count(di.id) as deviceNum \n" +
							"FROM device_info di \n" +
							"LEFT JOIN bas_area ba on di.province_id = ba.id \n" +
							"WHERE di.province_id is not null and ba.parent_id = 0\n" +
							"GROUP BY ba.id\n" +
							"ORDER BY deviceNum DESC\n" +
							"LIMIT 5";
		}else{
			sql=
					"select \n" +
							" ba.area_name as companyName,\n" +
							" count(di.id) as deviceNum \n" +
							"FROM device_info di \n" +
							"LEFT JOIN bas_area ba on di.province_id = ba.id \n" +
							"WHERE di.province_id is not null and ba.parent_id = 0 AND di.tenant_id ="+tenantId+"\n" +
							"GROUP BY ba.id\n" +
							"ORDER BY deviceNum DESC\n" +
							"LIMIT 5";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 工单次数排名（省份）
	 * @return
	 */
	@Override
	public List<Map<String, Object>> sfWorkRanking() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"SELECT \n" +
							" ba.area_name as areaName,\n" +
							" count(bw.id) as workOrderNum\n" +
							"FROM biz_workorder bw \n" +
							"LEFT JOIN device_info di on bw.device_id = di.id\n" +
							"LEFT JOIN bas_area ba on di.province_id = ba.id \n" +
							"WHERE di.province_id is not null and ba.parent_id = 0\n" +
							"GROUP BY ba.id\n" +
							"ORDER BY workOrderNum DESC\n" +
							"LIMIT 5";
		}else{
			sql=
					"SELECT \n" +
							" ba.area_name as areaName,\n" +
							" count(bw.id) as workOrderNum\n" +
							"FROM biz_workorder bw \n" +
							"LEFT JOIN device_info di on bw.device_id = di.id\n" +
							"LEFT JOIN bas_area ba on di.province_id = ba.id \n" +
							"WHERE di.province_id is not null and ba.parent_id = 0 AND bw.tenant_id = "+tenantId+"\n" +
							"GROUP BY ba.id\n" +
							"ORDER BY workOrderNum DESC\n" +
							"LIMIT 5";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 设备数量排名（客户）
	 * @return
	 */
	@Override
	public List<Map<String, Object>> khnumRanking() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"SELECT\n" +
							" st.company_name AS companyName,\n" +
							" count( 1 ) AS deviceNum \n" +
							"FROM\n" +
							" device_info bw\n" +
							"LEFT JOIN sys_tenant st ON bw.tenant_id = st.id \n" +
							"GROUP BY bw.tenant_id \n" +
							"ORDER BY deviceNum DESC \n" +
							"LIMIT 5";
		}else{
			sql=
					"SELECT\n" +
							" st.company_name AS companyName,\n" +
							" count( 1 ) AS deviceNum \n" +
							"FROM\n" +
							" device_info bw\n" +
							"LEFT JOIN sys_tenant st ON bw.tenant_id = st.id \n" +
							"WHERE bw.tenant_id is not null and bw.tenant_id ="+tenantId+"\n" +
							"GROUP BY bw.tenant_id \n" +
							"ORDER BY deviceNum DESC \n" +
							"LIMIT 5";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 工单次数排名（客户）
	 * @return
	 */
	@Override
	public List<Map<String, Object>> khworkRanking() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"SELECT\n" +
							" st.company_name AS companyName,\n" +
							" count( bw.id ) AS workOrderNum \n" +
							"FROM\n" +
							" biz_workorder bw\n" +
							"LEFT JOIN sys_tenant st ON bw.tenant_id = st.id \n" +
							"GROUP BY bw.tenant_id \n" +
							"ORDER BY workOrderNum DESC \n" +
							"LIMIT 5";
		}else{
			sql=
					"SELECT\n" +
							" st.company_name AS companyName,\n" +
							" count( bw.id ) AS workOrderNum \n" +
							"FROM\n" +
							" biz_workorder bw\n" +
							"LEFT JOIN sys_tenant st ON bw.tenant_id = st.id \n" +
							"WHERE bw.tenant_id is not null and bw.tenant_id = "+tenantId+"\n" +
							"GROUP BY bw.tenant_id \n" +
							"ORDER BY workOrderNum DESC \n" +
							"LIMIT 5";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 客户工单月度统计
	 * @return
	 */
	@Override
	public List<Map<String, Object>> khWorkOrder(String tenantName) {
		Long tenantId = getTenantId();
		String sql = "";
		if(!StringUtils.isEmpty(tenantName)){
			if(tenantId==null){
				sql = "SELECT\n" +
						" DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '%Y-%m' ) AS dataMonth,\n" +
						" count( bw.id ) as num \n" +
						"FROM\n" +
						" biz_workorder bw \n" +
						"LEFT JOIN sys_tenant st ON bw.tenant_id = st.id \n" +
						"WHERE st.company_name like '%"+tenantName+"%'\n" +
						"GROUP BY dataMonth \n" +
						"ORDER BY dataMonth ASC";
			}else{
				sql = "SELECT\n" +
						" DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '%Y-%m' ) AS dataMonth,\n" +
						" count( bw.id ) as num \n" +
						"FROM\n" +
						" biz_workorder bw \n" +
						"LEFT JOIN sys_tenant st ON bw.tenant_id = st.id \n" +
						"WHERE st.company_name like '%"+tenantName+"%' and bw.tenant_id = "+tenantId+"\n" +
						"GROUP BY dataMonth \n" +
						"ORDER BY dataMonth ASC";
			}

		}else{
			if(tenantId==null){
				sql = "SELECT\n" +
						" DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '%Y-%m' ) AS dataMonth,\n" +
						" count( bw.id ) as num \n" +
						"FROM\n" +
						" biz_workorder bw \n" +
						"GROUP BY dataMonth \n" +
						"ORDER BY dataMonth ASC";
			}else{
				sql = "SELECT\n" +
						" DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '%Y-%m' ) AS dataMonth,\n" +
						" count( bw.id ) as num \n" +
						"FROM\n" +
						" biz_workorder bw \n" +
						"WHERE bw.tenant_id = "+tenantId+"\n" +
						"GROUP BY dataMonth \n" +
						"ORDER BY dataMonth ASC";
			}
		}

		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 设备租赁数量
	 * @return
	 */
	@Override
	public List<Map<String, Object>> deviceLease(Integer queryType) {
		Long tenantId = getTenantId();
		String sql = "";
		String anaQueryType = anaQueryType(queryType);
		if(tenantId==null){
			sql=
					"SELECT\n" +
							" DATE_FORMAT( di.binding_date, '"+anaQueryType+"' ) AS leaseMonth,\n" +
							" count( di.id )  as  leaseNum\n" +
							"FROM device_info di \n" +
							"WHERE\n" +
							" di.binding_date IS NOT NULL\n" +
							"GROUP BY leaseMonth \n" +
							"ORDER BY leaseMonth ASC";
		}else{
			sql=
					"SELECT\n" +
							" DATE_FORMAT( di.binding_date, '"+anaQueryType+"' ) AS leaseMonth,\n" +
							" count( di.id )  as  leaseNum\n" +
							"FROM device_info di \n" +
							"WHERE\n" +
							" di.binding_date IS NOT NULL and di.tenant_id = "+tenantId+"\n" +
							"GROUP BY leaseMonth \n" +
							"ORDER BY leaseMonth ASC";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 产品类型月度统计
	 * @return
	 */
	@Override
	public List<Map<String,List<Map<String,Object>>>> productType() {

		List<Map<String,List<Map<String,Object>>>> resList = new ArrayList<>();
		Long tenantId = getTenantId();
		String sqlGetModelType = "";
		if(tenantId==null){
			sqlGetModelType=
					"SELECT DISTINCT\n" +
							" c.name as modelName,\n" +
							" c.id as modelId\n" +
							"FROM\n" +
							" product p\n" +
							" LEFT JOIN category c ON p.category = c.id \n" +
							"WHERE\n" +
							" p.category IS NOT NULL ";
		}else{
			sqlGetModelType=
					"SELECT DISTINCT\n" +
							" c.name as modelName,\n" +
							" c.id as modelId\n" +
							"FROM\n" +
							" product p\n" +
							" LEFT JOIN category c ON p.category = c.id \n" +
							"WHERE\n" +
							" p.category IS NOT NULL and p.tenant_id = "+tenantId+"\n";
		}
		List<Map<String,Object>> getModelType = entityManager.createNativeQuery(sqlGetModelType).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		if(!getModelType.isEmpty()){
			for(Map<String,Object> objectMap : getModelType){
				if(objectMap!=null){
					String modelId = objectMap.get("modelId").toString();
					String modelName = objectMap.get("modelName").toString();
					if(!StringUtils.isEmpty(modelId)){
						String sql = "";
						if(tenantId==null){
							sql=
									"SELECT\n" +
											" DATE_FORMAT( FROM_UNIXTIME( p.create_at / 1000 ), '%Y-%m' ) AS month,\n" +
											" count( p.id ) AS num \n" +
											"FROM\n" +
											" product p\n" +
											"LEFT JOIN category c ON p.category = c.id \n" +
											"WHERE\n" +
											" p.category IS NOT NULL AND p.category ="+modelId+"\n" +
											"GROUP BY month \n" +
											"ORDER BY month ASC";
						}else{
							sql=
									"SELECT\n" +
											" DATE_FORMAT( FROM_UNIXTIME( p.create_at / 1000 ), '%Y-%m' ) AS month,\n" +
											" count( p.id ) AS num \n" +
											"FROM\n" +
											" product p\n" +
											"LEFT JOIN category c ON p.category = c.id \n" +
											"WHERE\n" +
											" p.category IS NOT NULL AND p.category ="+modelId+" AND p.tenant_id = "+tenantId+"\n" +
											"GROUP BY month \n" +
											"ORDER BY month ASC";
						}
						List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
						Map<String,List<Map<String,Object>>> map = new HashMap<>();
						map.put(modelName,list);
						resList.add(map);
					}
				}
			}
		}

		return resList;
	}

	/**
	 * 出租时长分类统计
	 * @return
	 */
	@Override
	public List<Map<String, Object>> leaseDuration() {

		List<Map<String,Object>> resList = new ArrayList<>();

		float start = 0.0f;
		float end = 0.0f;
		String cluName = "";
		for(int i = 1;i<=7; i++){
			switch (i){
				case 1:
					start = 0.0f;
					end = 0.5f;
					cluName = "0.5年";
					break;
				case 2:
					start = 0.5f;
					end = 1.0f;
					cluName = "1年";
					break;
				case 3:
					start = 1.0f;
					end = 1.5f;
					cluName = "1.5年";
					break;
				case 4:
					start = 1.5f;
					end = 2.0f;
					cluName = "2年";
					break;
				case 5:
					start = 2.0f;
					end = 2.5f;
					cluName = "2.5年";
					break;
				case 6:
					start = 2.5f;
					end = 3.0f;
					cluName = "3年";
					break;
				case 7:
					start = 3.0f;
					end = 100.0f;
					cluName = "3年及以上";
					break;
				default:
					break;
			}
			Long tenantId = getTenantId();
			String sql = "";
			if(tenantId==null){
				sql=
						"select \n" +
								" count(res.id) as num\n" +
								"FROM (\n" +
								"SELECT\n" +
								"CASE\n" +
								"  WHEN \n" +
								"  binding_date IS NULL THEN 0\n" +
								"  ELSE DATEDIFF( NOW(), binding_date ) / 365\n" +
								"  END AS rentalDuration,\n" +
								"  id\n" +
								" FROM\n" +
								"device_info \n" +
								") res\n" +
								"WHERE res.rentalDuration between "+start+" and "+end+" ";
			}else{
				sql=
						"select \n" +
								" count(res.id) as num\n" +
								"FROM (\n" +
								"SELECT\n" +
								"CASE\n" +
								"  WHEN \n" +
								"  binding_date IS NULL THEN 0\n" +
								"  ELSE DATEDIFF( NOW(), binding_date ) / 365\n" +
								"  END AS rentalDuration,\n" +
								"  id\n" +
								" FROM\n" +
								"device_info where tenant_id = "+tenantId+"\n" +
								") res\n" +
								"WHERE res.rentalDuration between "+start+" and "+end;
			}
			List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();

			Map<String,Object> map = new HashMap<>();
			map.put(cluName,list.get(0).get("num"));
			resList.add(map);
		}
		return resList;
	}

	/**
	 * 设备状态-2
	 * @return
	 */
	@Override
	public List<Map<String, Object>> deviceStatus2() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"SELECT\n" +
							" state AS deviceStatus,\n" +
							" count( state ) AS deviceNum \n" +
							"FROM\n" +
							" device_info\n" +
							"GROUP BY state \n " +
							"UNION\n" +
							"SELECT\n" +
							" 'failure' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" biz_workorder \n" +
							"WHERE  type = 3 \n" +
							"UNION\n" +
							"SELECT\n" +
							" 'total' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" device_info ";
		}else{
			sql=
					"SELECT\n" +
							" state AS deviceStatus,\n" +
							" count( state ) AS deviceNum \n" +
							"FROM\n" +
							" device_info where tenant_id = "+tenantId+"\n" +
							"GROUP BY state \n" +
							"UNION\n" +
							"SELECT\n" +
							" 'failure' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" biz_workorder \n" +
							"WHERE type = 3 and tenant_id = "+tenantId+"\n" +
							"UNION\n" +
							"SELECT\n" +
							" 'total' AS deviceStatus,\n" +
							" count( 0 ) AS deviceNum \n" +
							"FROM\n" +
							" device_info where tenant_id = "+tenantId+"\n";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 告警统计
	 * @return
	 */
	@Override
	public List<Map<String, Object>> alertStatistics(Integer queryType) {
		String anaQueryType = anaQueryType(queryType);
		String queryStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern(dateFormatePattern(queryType)));
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"SELECT\n" +
							" count( 0 ) AS alertNum \n" +
							"FROM\n" +
							" alert_record \n" +
							"WHERE\n" +
							" DATE_FORMAT( FROM_UNIXTIME( alert_time / 1000 ), '"+anaQueryType+"' ) = '"+queryStr+"'";
		}else {
			sql=
					"SELECT\n" +
							" count( 0 ) AS alertNum \n" +
							"FROM\n" +
							" alert_record \n" +
							"WHERE\n" +
							" DATE_FORMAT( FROM_UNIXTIME( alert_time / 1000 ), '"+anaQueryType+"' ) = '"+queryStr+"' and tenant_id = "+tenantId;
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

	/**
	 * 工单统计
	 * @return
	 */
	@Override
	public List<Map<String,List<Map<String,Object>>>> workStatistics(Integer queryType) {
		List<Map<String,List<Map<String,Object>>>> resList = new ArrayList<>();
		String anaQueryType = anaQueryType(queryType);
		String queryStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern(dateFormatePattern(queryType)));
		Long tenantId = getTenantId();
		String sqlGetModelType = "";
		if(tenantId==null){
			sqlGetModelType=
					"SELECT \n" +
							"min(sdd.dict_label) AS workOrderType,\n" +
							"min(sdd.dict_value) AS workOrderValue \n" +
							"FROM\n" +
							" biz_workorder bw\n" +
							" LEFT JOIN sys_dict_data sdd ON bw.type = sdd.dict_value  AND sdd.dict_type = 'biz_workorder_type' \n" +
							"GROUP BY bw.type";
		}else{
			sqlGetModelType=
					"SELECT \n" +
							"min(sdd.dict_label) AS workOrderType,\n" +
							"min(sdd.dict_value) AS workOrderValue \n" +
							"FROM\n" +
							" biz_workorder bw\n" +
							" LEFT JOIN sys_dict_data sdd ON bw.type = sdd.dict_value \n" +
							" AND sdd.dict_type = 'biz_workorder_type' AND bw.tenant_id = "+tenantId+"\n" +
							"GROUP BY bw.type";
		}
		List<Map<String,Object>> getModelType = entityManager.createNativeQuery(sqlGetModelType).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		if(!getModelType.isEmpty()){
			for(Map<String,Object> objectMap : getModelType){
				if(objectMap!=null){
					String modelId = objectMap.get("workOrderValue").toString();
					String modelName = objectMap.get("workOrderType").toString();
					if(!StringUtils.isEmpty(modelId)){
						String sql= "";
						if(tenantId==null){
							sql = "SELECT\n" +
									" DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '"+anaQueryType+"' ) AS xz,\n" +
									" count( bw.id ) AS number \n" +
									"FROM\n" +
									" biz_workorder bw\n" +
									"WHERE " +
									" bw.type = "+modelId+" \n" +
									" AND DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '"+anaQueryType+"' ) = '"+queryStr+"' \n" +
									"GROUP BY xz";
						}else{
							sql = "SELECT\n" +
									" DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '"+anaQueryType+"' ) AS xz,\n" +
									" count( bw.id ) AS number \n" +
									"FROM\n" +
									" biz_workorder bw\n" +
									"WHERE " +
									" bw.type = "+modelId+" \n" +
									" AND DATE_FORMAT( FROM_UNIXTIME( bw.create_at / 1000 ), '"+anaQueryType+"' ) = '"+queryStr+"' \n" +
									" AND bw.tenant_id = "+tenantId+"\n" +
									"GROUP BY xz";
						}

						List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
						Map<String,List<Map<String,Object>>> map = new HashMap<>();
						map.put(modelName,list);
						resList.add(map);
					}
				}
			}
		}
		return resList;
	}

	/**
	 * 保养提醒
	 * @return
	 */
	@Override
	public List<Map<String, Object>> maintenanceReminder() {
		Long tenantId = getTenantId();
		String sql = "";
		if(tenantId==null){
			sql=
					"select\n" +
							"case when pm.`name` is not null then pm.`name` else '无' end as modelName,\n" +
							"case when dp.part_name is not null then dp.part_name else '无' end as partsName,\n" +
							"di.device_name as deviceName,\n" +
							"date_add(di.binding_date, interval (case when bmu.unit_name='年' then dp.maintenance_period * 365 \n" +
							"     when bmu.unit_name='月' then dp.maintenance_period * 30\n" +
							"     when bmu.unit_name='天' then dp.maintenance_period \n" +
							"     end - 7 ) day) as maintenanceDate,\n" +
							" DATE_FORMAT((case when bmu.unit_name='年' then di.binding_date+ interval dp.maintenance_period year \n" +
							"     when bmu.unit_name='月' then di.binding_date+ interval dp.maintenance_period month \n" +
							"     when bmu.unit_name='天' then di.binding_date+ interval dp.maintenance_period day\n" +
							"     end ),'%Y-%m-%d') as maintenanceDays\n" +
							"from device_info di \n" +
							"left join product_model pm on di.model_id = pm.id\n" +
							"left join device_info_parts dip on di.id = dip .device_id\n" +
							"left join device_parts dp on dp.id = dip.device_parts_id\n" +
							"left join bas_maintenance_unit bmu on dp.unit_id = bmu.id";
		}else{
			sql=
					"select\n" +
							"case when pm.`name` is not null then pm.`name` else '无' end as modelName,\n" +
							"case when dp.part_name is not null then dp.part_name else '无' end as partsName,\n" +
							"di.device_name as deviceName,\n" +
							"date_add(di.binding_date, interval (case when bmu.unit_name='年' then dp.maintenance_period * 365 \n" +
							"     when bmu.unit_name='月' then dp.maintenance_period * 30\n" +
							"     when bmu.unit_name='天' then dp.maintenance_period \n" +
							"     end - 7 ) day) as maintenanceDate,\n" +
							" DATE_FORMAT((case when bmu.unit_name='年' then di.binding_date+ interval dp.maintenance_period year \n" +
							"     when bmu.unit_name='月' then di.binding_date+ interval dp.maintenance_period month \n" +
							"     when bmu.unit_name='天' then di.binding_date+ interval dp.maintenance_period day\n" +
							"     end ),'%Y-%m-%d') as maintenanceDays\n" +
							"from device_info di \n" +
							"left join product_model pm on di.model_id = pm.id\n" +
							"left join device_info_parts dip on di.id = dip .device_id\n" +
							"left join device_parts dp on dp.id = dip.device_parts_id\n" +
							"left join bas_maintenance_unit bmu on dp.unit_id = bmu.id\n" +
							"WHERE di.tenant_id = "+tenantId+"";
		}
		List<Map<String,Object>> list = entityManager.createNativeQuery(sql).unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
		return list;
	}

}
